unit U_GenerateSql;

interface

uses
  Forms, SysUtils, Classes, DBXpress, FMTBcd, DB, SqlExpr,
  StdCtrls, Provider, DBClient, SimpleDS, Variants, Math, StrUtils, ADODB;

type
  TCommandType = (cmdInsert, cmdUpDate, cmdDelete);

type
  TUsaGenerateSql  = class(TComponent)
  private
    { Private declarations }
    fCommandType: TCommandType;
    fSQLConnection: TADOConnection;
    fTabela: String;
    fFieldsName: TStringList;
    fFieldsValue: TStringList;
    fCondicaoWhere: TStringList;
    fMessageErro: String;
    fSql: String;

    procedure SetFieldsName(Value: TStringList);
    procedure SetFieldsValue(Value: TStringList);
    procedure SetCondicaoWhere(Value: TStringList);

    procedure SetConnection(Value: TADOConnection);

    function GetSqlInsert: String;
    function GetSqlUpDate: String;
    function GetSqlDelete: String;

    function FormataValue(AValue: Variant; AMascara: String=''; ASeparadorDecimal: Char='.'; ASeparadorMilhar: Char=#0):String;
    function RemoveFormat(Text: String): String;
  protected
    { Protected declarations }
    property Sql: String read fSql write fSql;
    property MessageErro: String read fMessageErro write fMessageErro;
  public
    { Public declarations }
    procedure Prepare;
    function Execute(UseTransaction : boolean = false):  Boolean;
    procedure SetValue(AValue:Variant; AFieldName:String=''; AMascara:String=''; ASeparadorDecimal: Char='.'; ASeparadorMilhar: Char=#0);
    function GetSql: String;
    function GetMessageErro: String;

    constructor Create(AOwner: TComponent); override;
    destructor Destroy; Override;
  published
    { Published declarations }
    property CommandType: TCommandType read fCommandType write fCommandType default cmdInsert;
    property SQLConnection: TADOConnection read fSQLConnection write SetConnection;

    property Tabela       :   String            read fTabela              write fTabela;
    property FieldsName   :   TStringList       read fFieldsName          write SetFieldsName;
    property FieldsValue  :   TStringList       read fFieldsValue         write SetFieldsValue;
    property CondicaoWhere:   TStringList       read fCondicaoWhere       write SetCondicaoWhere;
  end;

implementation

{ TUsaGenerateSql }
constructor TUsaGenerateSql.Create(AOwner: TComponent);
begin
  inherited;
  fFieldsName    := TStringList.Create;
  fFieldsValue   := TStringList.Create;
  fCondicaoWhere := TStringList.Create;
end;

destructor TusaGenerateSql.Destroy;
begin
  fFieldsName.Destroy;
  fFieldsValue.Destroy;
  fCondicaoWhere.Destroy;
  inherited Destroy;
end;

function TUsaGenerateSql.Execute(UseTransaction : boolean): Boolean;
Var
  qryTemp: TADOQuery;
begin
  Result := False;
  case CommandType of
    cmdInsert:
    begin
      fSql := GetSqlInsert;
    end;
    cmdUpDate:
    begin
      fSql := GetSqlUpDate;
    end;
    cmdDelete:
    begin
      fSql := GetSqlDelete;
    end;
  end;

  qryTemp := TADOQuery.Create(Application);
  qryTemp.Connection := fSQLConnection;

  //--- inicia transacao
  if UseTransaction then
    fSQLConnection.BeginTrans;

  try
    fMessageErro := '';
    qryTemp.SQL.Clear;
    qryTemp.SQL.Add(fSQL);
    qryTemp.ExecSQL;
    qryTemp.Free;

    //--- comita transacao
    if UseTransaction then
      fSQLConnection.CommitTrans;

    Result := True;
  except
    on E: Exception do
    begin
      qryTemp.Free;
      fMessageErro := E.Message;

      //--- rollback transacao
      if UseTransaction then
        fSQLConnection.RollbackTrans;
    end;
  end;
end;

function TUsaGenerateSql.FormataValue(AValue: Variant; AMascara: String;  ASeparadorDecimal, ASeparadorMilhar: Char): String;
var
  sMascara:String;
  fsFloatSetings: TFormatSettings;
begin
  fsFloatSetings.DecimalSeparator := ASeparadorDecimal;
  fsFloatSetings.ThousandSeparator := ASeparadorMilhar;

  if VarType(AValue) = varEmpty then result := QuotedStr('');
  if VarType(AValue) = varNull then result := 'Null';
  if VarType(AValue) = varInteger then result := VarToStrDef(AValue,'0');
  if VarType(AValue) = varBoolean then Result := IfThen(AValue,QuotedStr('1'),QuotedStr('0'));
  if VarType(AValue) = varString then Result := QuotedStr(AValue);
  if VarType(AValue) = varByte then result := VarToStrDef(AValue,'0');

  if (VarType(AValue) = varDouble) or (VarType(AValue) = varCurrency) then
  begin
     sMascara := IfThen(AMascara<>'', AMascara, '##0.00');
     result := QuotedStr(FormatFloat(sMascara,AValue,fsFloatSetings));
  end;

  if VarType(AValue) = varDate then
  begin
     if AnsiPos('/', VarToStr(AValue)) <> 0 then
     begin
       //--- Tipo Date
       sMascara := IfThen(AMascara<>'', AMascara,   'yyyy/mm/dd');
       if AValue<>0 then result := QuotedStr(FormatDateTime(sMascara,AValue))
       else result := QuotedStr('null');
     end
     else
     begin
       //--- tipo Time
       sMascara := IfThen(AMascara<>'', AMascara,   'hh:mm:ss');
       Result := QuotedStr(FormatDateTime(sMascara, AValue));
       if StrToFloat(RemoveFormat(Result)) = 0 then Result := 'null';
     end;
  end;
end;

function TUsaGenerateSql.GetMessageErro: String;
begin
  Result := fMessageErro;
end;

function TUsaGenerateSql.GetSql: String;
begin
  Result := fSql;
end;

function TUsaGenerateSql.GetSqlDelete: String;
Var
  sWhere: String;
begin
  if fCondicaoWhere.Text <> '' then sWhere := ' WHERE ' + fCondicaoWhere.Text
  else sWhere := '';
  Result := 'DELETE FROM ' + fTabela + sWhere;
end;

function TUsaGenerateSql.GetSqlInsert: String;
var
  X: Integer;
  sSQLFields: String;
  sSQLValues: String;
begin
  //--- GET CAMPOS TABELA
  sSQLFields := '';
  for X := 0 to fFieldsName.Count - 1 do
  begin
    if sSQLFields = '' then  sSQLFields := fFieldsName.Strings[X]
    else sSQLFields := sSQLFields + ', ' + fFieldsName.Strings[X];
  end;

  //--- VALOR CAMPOS
  sSQLValues := '';
  for X := 0 to fFieldsValue.Count - 1 do
  begin
    if sSQLValues = '' then  sSQLValues := fFieldsValue.Strings[X]
    else sSQLValues := sSQLValues + ', ' + fFieldsValue.Strings[X];
  end;
  Result := 'INSERT INTO ' + fTabela + ' (' + sSQLFields + ') VALUES (' + sSQLValues + ')';
end;

function TUsaGenerateSql.GetSqlUpDate: String;
var
  X: Integer;
  sSQL: String;
  sWhere: String;
begin
  //--- GET CAMPOS TABELA
  sSQL := '';
  for X := 0 to fFieldsName.Count - 1 do
  begin
    if sSQL = '' then  sSQL := fFieldsName.Strings[X] + '= ' + fFieldsValue.Strings[X]
    else sSQL := sSQL + ', ' + fFieldsName.Strings[X] + '= ' + fFieldsValue.Strings[X];
  end;

  if fCondicaoWhere.Text <> '' then sWhere := ' WHERE ' + fCondicaoWhere.Text
  else sWhere := '';

  Result := 'UPDATE ' + fTabela + ' SET ' + sSQL + sWhere;
end;

procedure TUSaGenerateSql.Prepare;
begin
  fTabela := '';
  fFieldsName.Clear;
  fFieldsValue.Clear;
  fCondicaoWhere.Clear;
  fSql := '';
  fMessageErro := '';
end;

function TUsaGenerateSql.RemoveFormat(Text: String): String;
Var
  x:Integer;
  NotFormat: String;
  Letra: Char;
Begin
  NotFormat := '';
  for x:=1 To Length(Text) Do
  begin
    Letra:=Text[x];
    if (Letra in ['0'..'9']) or (Letra in ['A'..'Z']) or (Letra in ['a'..'z']) then
      NotFormat := NotFormat+Letra;
  end;
  Result:=NotFormat;
end;

procedure TUsaGenerateSql.SetConnection(Value: TADOConnection);
begin
  fSQLConnection := Value;
end;

procedure TUsaGenerateSql.SetFieldsName(Value: TStringList);
begin
  fFieldsName.Assign(Value);
end;

procedure TUsaGenerateSql.SetFieldsValue(Value: TStringList);
begin
  fFieldsValue.Assign(Value);
end;

procedure TUsaGenerateSql.SetValue(AValue: Variant; AFieldName, AMascara: String;
  ASeparadorDecimal, ASeparadorMilhar: Char);
begin
  fFieldsName.Add(AFieldName);
  fFieldsValue.Add(FormataValue(AValue, aMascara, ASeparadorDecimal, ASeparadorMilhar));
end;

procedure TUsaGenerateSql.SetCondicaoWhere(Value: TStringList);
begin
  fCondicaoWhere.Assign(Value);
end;

end.





